package sync.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import sync.bean.PKBean;
import sync.bean.PKDataBean;
import sync.bean.PKUpdateBean;
import sync.bean.PKzsBean;
import sync.bean.SSCBean;
import sync.bean.SSCFiveUpdateBean;
import sync.bean.SSCLastResultBean;
import sync.bean.SSCPlanBean;

public class DBHelper {

	// 插入ssc数据
	public static boolean insertSSC(SSCBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "insert into ssc(qihao,tenthousand,thousand,hundred,ten,one,opentime) values('"
					+ bean.getQihao() + "' ,'"
					+ bean.getTenthousand() + "' ,'"
					+ bean.getThousand() + "' ,'"
					+ bean.getHundred() + "' ,'"
					+ bean.getTen() + "' ,'"
					+ bean.getOne() + "' ,'"
					+ bean.getOpentime() +"')";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	// 获取ssc对应期号的数
	public static SSCBean getRecordByQihao(String qihao){
		Connection conn;
		SSCBean bean = null;
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao,tenthousand,thousand,hundred,ten,one,opentime FROM ssc where qihao = "+qihao;
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				bean = new SSCBean();
				bean.setTenthousand(result.getInt("tenthousand"));
				bean.setThousand(result.getInt("thousand"));
				bean.setHundred(result.getInt("hundred"));
				bean.setTen(result.getInt("ten"));
				bean.setOne(result.getInt("one"));
				bean.setQihao(result.getString("qihao"));
			}  
			result.close();  
			stament.close(); 
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	// 获取sscplan对应期号的数
	public static SSCLastResultBean getPlanRecordByQihao(String qihao,int position){
		Connection conn;
		SSCLastResultBean bean = null;
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao,fivenum,fournum,threenum,twonum,levelottnum FROM ssc_plan where qihao = '"+qihao+"' and position = "+position;
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				bean = new SSCLastResultBean();
				bean.setFivenum(result.getString("fivenum"));
				bean.setFournum(result.getString("fournum"));
				bean.setThreenum(result.getString("threenum"));
				bean.setQihao(result.getString("qihao"));
				bean.setTwonum(result.getString("twonum"));
				bean.setLevelottnum(result.getString("levelottnum"));
			}  
			result.close();  
			stament.close(); 
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	// 获取第一条记录期号
	public static String getLastRecordQihao(){
		Connection conn;
		String qihao = "";
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao FROM ssc order by id desc limit 1";
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				qihao = result.getString("qihao");
			}  
			result.close();  
			stament.close(); 
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return qihao;
	}

	// 插入PK数据
	public static boolean insertPK(PKBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "insert into pk(qihao,one,two,three,four,five,six,seven,eight,nine,ten,opentime) values('"
					+ bean.getQihao() + "' ,'"
					+ bean.getOne() + "' ,'"
					+ bean.getTwo() + "' ,'"
					+ bean.getThree() + "' ,'"
					+ bean.getFour() + "' ,'"
					+ bean.getFive() + "' ,'"
					+ bean.getSix() + "' ,'"
					+ bean.getSeven() + "' ,'"
					+ bean.getEight() + "' ,'"
					+ bean.getNine() + "' ,'"
					+ bean.getTen() + "' ,'"
					+ bean.getOpentime() +"')";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	// 更新上一期数据
	public static boolean updateLastSSCRecord(SSCFiveUpdateBean bean,int position){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "update ssc_plan set fivewr='"+bean.getFivewr()+"',levelottwr = '"+bean.getLevelottwr()+"',twowr='"+bean.getTwowr()+"',fourwr='"
					+bean.getFourwr()+"',threewr='"+bean.getThreewr()+"',resultnum = '"+bean.getResultnum()+"' where qihao = '"+bean.getQihao()+"' and position = "+position;
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	// 查询是否有想同的数据
	public static String getSSCPlanQihao(int position){
		Connection conn;
		String qihao = "";
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao FROM ssc_plan where position = "+position+" order by id desc limit 1";
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				qihao = result.getString("qihao");
			}  
			result.close();  
			stament.close(); 
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return qihao;
	}

	public static boolean insertSSCPlan(SSCPlanBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "insert into ssc_plan(qihao,levelone,leveltwo,levelthree,levelfour,levelfive,"
					+ "levelsix,levelseven,leveleight,levelnine,levelten,threenum,threewr,fournum,fourwr,fivenum,fivewr,twonum,twowr,levelottnum,levelottwr,position,opentime) values('"
					+ bean.getQihao() + "' ,'"
					+ bean.getLevelone() + "' ,'"
					+ bean.getLeveltwo() + "' ,'"
					+ bean.getLevelthree() + "' ,'"
					+ bean.getLevelfour()+ "' ,'"
					+ bean.getLevelfive() + "' ,'"
					+ bean.getLevelsix() + "' ,'"
					+ bean.getLevelseven() + "' ,'"
					+ bean.getLeveleight() + "' ,'"
					+ bean.getLevelnine() + "' ,'"
					+ bean.getLevelten() + "' ,'"
					+ bean.getThreenum()+ "' ,'"
					+ bean.getThreewr() + "' ,'"
					+ bean.getFournum() + "' ,'"
					+ bean.getFourwr() + "' ,'"
					+ bean.getFivenum() + "' ,'"
					+ bean.getFivewr() + "' ,'"
					+ bean.getTwonum() + "' ,'"
					+ bean.getTwowr() + "' ,'"
					+ bean.getLevelott() + "' ,'"
					+ bean.getLevelottwr() + "' ,'"
					+ bean.getPostion() + "' ,'"
					+ bean.getOpentime() +"')";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}



	/***************************************************************     pk的数据库操作 分割线     **********************************************************************************************/
	// 获取pk第一条数据
	public static String getPKLastRecordQihao(){
		Connection conn;
		String qihao = "";
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao FROM pk_sum order by id desc limit 1";
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				qihao = result.getString("qihao");
			}
			result.close();
			stament.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return qihao;
	}

	// 根据期号获取数据查询是否存在
	public static String getPKRecordByQihao(String tableName,String pqihao){
		Connection conn;
		String qihao = "";
		try {
			conn = ConnectionPool.getInstance().getConnection();
			String sql = "SELECT qihao FROM "+tableName+" order where qihao = '"+pqihao+"';";
			Statement stament = conn.createStatement();
			ResultSet result = stament.executeQuery(sql);
			while(result.next()){//判断是否还有下一行
				qihao = result.getString("qihao");
			}
			result.close();
			stament.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return qihao;
	}
	
	// 获取pk0-10对应期号bean
		public static PKUpdateBean getPKUpdateByQihao(String qihao,String tableName){
			Connection conn;
			PKUpdateBean bean = null;
			try {
				conn = ConnectionPool.getInstance().getConnection();
				String sql = "SELECT qihao,one,two,three,four,five,six,seven,eight,nine,ten FROM "+tableName+" where qihao = '"+qihao+"';";
				Statement stament = conn.createStatement();
				ResultSet result = stament.executeQuery(sql);
				while(result.next()){//判断是否还有下一行
					bean = new PKUpdateBean();
					bean.setOne(result.getString("one"));
					bean.setTwo(result.getString("two"));
					bean.setThree(result.getString("three"));
					bean.setFour(result.getString("four"));
					bean.setFive(result.getString("five"));
					bean.setSix(result.getString("six"));
					bean.setSeven(result.getString("seven"));
					bean.setEight(result.getString("eight"));
					bean.setNine(result.getString("nine"));
					bean.setTen(result.getString("ten"));
				}  
				result.close();  
				stament.close(); 
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return bean;
		}
	
	
	// 更新上一期数据
	public static boolean updateLastPKRecord(String tablename,PKUpdateBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "update "+tablename+" set onewr='"+bean.getOnewr()+"',twowr = '"+bean.getTwowr()+"',threewr='"+bean.getThreewr()+"',fourwr='"
					+bean.getFourwr()+"',fivewr='"+bean.getFivewr()+"',sixwr='"+bean.getSixwr()+
					"',sevenwr='"+bean.getSevenwr()+"',eightwr='"+bean.getEightwr()+
					"',ninewr='"+bean.getNinewr()+"',tenwr='"+bean.getTenwr()+"',result = '"+bean.getResult()+"' where qihao = '"+bean.getQihao()+"';";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	// 十个表的数据插入都从这里来
	public static boolean insertPkOneToTen(PKDataBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "insert into "+bean.getTable_name()+"(qihao,one,two,three,four,five,"
					+ "six,seven,eight,nine,ten) values('"
					+ bean.getQihao() + "' ,'"
					+ bean.getOne()+ "' ,'"
					+ bean.getTwo() + "' ,'"
					+ bean.getThree() + "' ,'"
					+ bean.getFour() + "' ,'"
					+ bean.getFive() + "' ,'"
					+ bean.getSix()+ "' ,'"
					+ bean.getSeven() + "' ,'"
					+ bean.getEight() + "' ,'"
					+ bean.getNine() + "' ,'"
					+ bean.getTen() +"')";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	// 走势插入
	public static boolean insertPkzs(PKzsBean bean){
		Connection conn;
		try{
			conn = ConnectionPool.getInstance().getConnection();
			Statement statemenet = conn.createStatement();
			String sql = "insert into pk_sum(qihao,one,two,sumot,area,sumlevel) values('"
					+ bean.getQihao() + "' ,'"
					+ bean.getOne()+ "' ,'"
					+ bean.getTwo() + "' ,'"
					+ bean.getSumot() + "' ,'"
					+ bean.getArea() + "' ,'"
					+ bean.getSumlevel() +"')";
			statemenet.execute(sql);
			statemenet.close();
			conn.close();
		}catch(Exception ex){
			ex.printStackTrace();
			return false;
		}
		return true;
	}

}
